﻿using Api.Common;
using Api.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Api.DAL
{
    /// <summary>
    /// 数据访问类:interview
    /// </summary>
    public partial class interview
    {
        private string databaseprefix = "eazy_"; //数据库表名前缀
        public interview()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "interview");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists_I(int _id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "interview");
            strSql.Append(" where instance_id=@instance_id");
            SqlParameter[] parameters = {
                    new SqlParameter("@instance_id", SqlDbType.Int,4)
            };
            parameters[0].Value = _id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Models.interview model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into " + databaseprefix + "interview(");
            strSql.Append("instance_id,ask_time,how,ask_address,b_name,b_sex,b_age,b_code,b_company,b_zhiwu,b_zhuzhi,b_mobile,d_name,d_code,d_note,d_there,d_company,w_company,w_name1,w_name2,w_code1,w_code2,w_question,content,d_signature,b_signature,j_signature,sub_time,end_time)");
            strSql.Append(" values (");
            strSql.Append("@instance_id,@ask_time,@how,@ask_address,@b_name,@b_sex,@b_age,@b_code,@b_company,@b_zhiwu,@b_zhuzhi,@b_mobile,@d_name,@d_code,@d_note,@d_there,@d_company,@w_company,@w_name1,@w_name2,@w_code1,@w_code2,@w_question,@content,@d_signature,@b_signature,@j_signature,@sub_time,@end_time)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@instance_id", SqlDbType.Int,4),
                    new SqlParameter("@ask_time", SqlDbType.DateTime),
                    new SqlParameter("@how", SqlDbType.Int,4),
                    new SqlParameter("@ask_address", SqlDbType.NVarChar,255),
                    new SqlParameter("@b_name", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_sex", SqlDbType.Int,4),
                    new SqlParameter("@b_age", SqlDbType.Int,4),
                    new SqlParameter("@b_code", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_company", SqlDbType.NVarChar,100),
                    new SqlParameter("@b_zhiwu", SqlDbType.NVarChar,100),
                    new SqlParameter("@b_zhuzhi", SqlDbType.NVarChar,255),
                    new SqlParameter("@b_mobile", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_name", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_code", SqlDbType.NVarChar,50),
                    new SqlParameter("@d_note", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_there", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_company", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_company", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_name1", SqlDbType.NVarChar,20),
                    new SqlParameter("@w_name2", SqlDbType.NVarChar,20),
                    new SqlParameter("@w_code1", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_code2", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_question", SqlDbType.NVarChar,255),
                    new SqlParameter("@content", SqlDbType.NText),
                    new SqlParameter("@d_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@j_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@sub_time", SqlDbType.DateTime),
                    new SqlParameter("@end_time", SqlDbType.DateTime)};
            parameters[0].Value = model.instance_id;
            parameters[1].Value = model.ask_time;
            parameters[2].Value = model.how;
            parameters[3].Value = model.ask_address;
            parameters[4].Value = model.b_name;
            parameters[5].Value = model.b_sex;
            parameters[6].Value = model.b_age;
            parameters[7].Value = model.b_code;
            parameters[8].Value = model.b_company;
            parameters[9].Value = model.b_zhiwu;
            parameters[10].Value = model.b_zhuzhi;
            parameters[11].Value = model.b_mobile;
            parameters[12].Value = model.d_name;
            parameters[13].Value = model.d_code;
            parameters[14].Value = model.d_note;
            parameters[15].Value = model.d_there;
            parameters[16].Value = model.d_company;
            parameters[17].Value = model.w_company;
            parameters[18].Value = model.w_name1;
            parameters[19].Value = model.w_name2;
            parameters[20].Value = model.w_code1;
            parameters[21].Value = model.w_code2;
            parameters[22].Value = model.w_question;
            parameters[23].Value = model.content;
            parameters[24].Value = model.d_signature;
            parameters[25].Value = model.b_signature;
            parameters[26].Value = model.j_signature;
            parameters[27].Value = model.sub_time;
            parameters[28].Value = model.end_time;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Models.interview model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "interview set ");
            strSql.Append("instance_id=@instance_id,");
            strSql.Append("ask_time=@ask_time,");
            strSql.Append("how=@how,");
            strSql.Append("ask_address=@ask_address,");
            strSql.Append("b_name=@b_name,");
            strSql.Append("b_sex=@b_sex,");
            strSql.Append("b_age=@b_age,");
            strSql.Append("b_code=@b_code,");
            strSql.Append("b_company=@b_company,");
            strSql.Append("b_zhiwu=@b_zhiwu,");
            strSql.Append("b_zhuzhi=@b_zhuzhi,");
            strSql.Append("b_mobile=@b_mobile,");
            strSql.Append("d_name=@d_name,");
            strSql.Append("d_code=@d_code,");
            strSql.Append("d_note=@d_note,");
            strSql.Append("d_there=@d_there,");
            strSql.Append("d_company=@d_company,");
            strSql.Append("w_company=@w_company,");
            strSql.Append("w_name1=@w_name1,");
            strSql.Append("w_name2=@w_name2,");
            strSql.Append("w_code1=@w_code1,");
            strSql.Append("w_code2=@w_code2,");
            strSql.Append("w_question=@w_question,");
            strSql.Append("content=@content,");
            strSql.Append("d_signature=@d_signature,");
            strSql.Append("b_signature=@b_signature,");
            strSql.Append("j_signature=@j_signature,");
            strSql.Append("sub_time=@sub_time,");
            strSql.Append("end_time=@end_time");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@instance_id", SqlDbType.Int,4),
                    new SqlParameter("@ask_time", SqlDbType.DateTime),
                    new SqlParameter("@how", SqlDbType.Int,4),
                    new SqlParameter("@ask_address", SqlDbType.NVarChar,255),
                    new SqlParameter("@b_name", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_sex", SqlDbType.Int,4),
                    new SqlParameter("@b_age", SqlDbType.Int,4),
                    new SqlParameter("@b_code", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_company", SqlDbType.NVarChar,100),
                    new SqlParameter("@b_zhiwu", SqlDbType.NVarChar,100),
                    new SqlParameter("@b_zhuzhi", SqlDbType.NVarChar,255),
                    new SqlParameter("@b_mobile", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_name", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_code", SqlDbType.NVarChar,50),
                    new SqlParameter("@d_note", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_there", SqlDbType.NVarChar,20),
                    new SqlParameter("@d_company", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_company", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_name1", SqlDbType.NVarChar,20),
                    new SqlParameter("@w_name2", SqlDbType.NVarChar,20),
                    new SqlParameter("@w_code1", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_code2", SqlDbType.NVarChar,50),
                    new SqlParameter("@w_question", SqlDbType.NVarChar,255),
                    new SqlParameter("@content", SqlDbType.NText),
                    new SqlParameter("@d_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@b_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@j_signature", SqlDbType.NVarChar,20),
                    new SqlParameter("@sub_time", SqlDbType.DateTime),
                    new SqlParameter("@end_time", SqlDbType.DateTime),
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.instance_id;
            parameters[1].Value = model.ask_time;
            parameters[2].Value = model.how;
            parameters[3].Value = model.ask_address;
            parameters[4].Value = model.b_name;
            parameters[5].Value = model.b_sex;
            parameters[6].Value = model.b_age;
            parameters[7].Value = model.b_code;
            parameters[8].Value = model.b_company;
            parameters[9].Value = model.b_zhiwu;
            parameters[10].Value = model.b_zhuzhi;
            parameters[11].Value = model.b_mobile;
            parameters[12].Value = model.d_name;
            parameters[13].Value = model.d_code;
            parameters[14].Value = model.d_note;
            parameters[15].Value = model.d_there;
            parameters[16].Value = model.d_company;
            parameters[17].Value = model.w_company;
            parameters[18].Value = model.w_name1;
            parameters[19].Value = model.w_name2;
            parameters[20].Value = model.w_code1;
            parameters[21].Value = model.w_code2;
            parameters[22].Value = model.w_question;
            parameters[23].Value = model.content;
            parameters[24].Value = model.d_signature;
            parameters[25].Value = model.b_signature;
            parameters[26].Value = model.j_signature;
            parameters[27].Value = model.sub_time;
            parameters[28].Value = model.end_time;
            parameters[29].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void UpdateFields(int id, string strwhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "interview set " + strwhere);
            strSql.Append(" where id=" + id);
            DbHelperSQL.GetSingle(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databaseprefix + "interview ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databaseprefix + "interview ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Models.interview GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,ask_time,end_time,how,ask_address,b_name,b_sex,b_age,b_code,b_company,b_zhiwu,b_zhuzhi,b_mobile,d_name,d_code,d_note,d_there,d_company,w_company,w_name1,w_name2,w_code1,w_code2,w_question,content,d_signature,b_signature,j_signature,sub_time,instance_id ");
            strSql.Append(" from " + databaseprefix + "interview");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            Models.interview model = new Models.interview();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Models.interview GetModel_I(int _id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,ask_time,end_time,how,ask_address,b_name,b_sex,b_age,b_code,b_company,b_zhiwu,b_zhuzhi,b_mobile,d_name,d_code,d_note,d_there,d_company,w_company,w_name1,w_name2,w_code1,w_code2,w_question,content,d_signature,b_signature,j_signature,sub_time,instance_id ");
            strSql.Append(" from " + databaseprefix + "interview");
            strSql.Append(" where instance_id=@instance_id");
            SqlParameter[] parameters = {
                    new SqlParameter("@instance_id", SqlDbType.Int,4)
            };
            parameters[0].Value = _id;

            Models.interview model = new Models.interview();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Models.interview DataRowToModel(DataRow row)
        {
            Models.interview model = new Models.interview();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["ask_time"] != null && row["ask_time"].ToString() != "")
                {
                    model.ask_time = DateTime.Parse(row["ask_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }
                if (row["how"] != null && row["how"].ToString() != "")
                {
                    model.how = int.Parse(row["how"].ToString());
                }
                if (row["ask_address"] != null)
                {
                    model.ask_address = row["ask_address"].ToString();
                }
                if (row["b_name"] != null)
                {
                    model.b_name = row["b_name"].ToString();
                }
                if (row["b_sex"] != null && row["b_sex"].ToString() != "")
                {
                    model.b_sex = int.Parse(row["b_sex"].ToString());
                }
                if (row["b_age"] != null && row["b_age"].ToString() != "")
                {
                    model.b_age = int.Parse(row["b_age"].ToString());
                }
                if (row["b_code"] != null)
                {
                    model.b_code = row["b_code"].ToString();
                }
                if (row["b_company"] != null)
                {
                    model.b_company = row["b_company"].ToString();
                }
                if (row["b_zhiwu"] != null)
                {
                    model.b_zhiwu = row["b_zhiwu"].ToString();
                }
                if (row["b_zhuzhi"] != null)
                {
                    model.b_zhuzhi = row["b_zhuzhi"].ToString();
                }
                if (row["b_mobile"] != null)
                {
                    model.b_mobile = row["b_mobile"].ToString();
                }
                if (row["d_name"] != null)
                {
                    model.d_name = row["d_name"].ToString();
                }
                if (row["d_code"] != null)
                {
                    model.d_code = row["d_code"].ToString();
                }
                if (row["d_note"] != null)
                {
                    model.d_note = row["d_note"].ToString();
                }
                if (row["d_there"] != null)
                {
                    model.d_there = row["d_there"].ToString();
                }
                if (row["d_company"] != null)
                {
                    model.d_company = row["d_company"].ToString();
                }
                if (row["w_company"] != null)
                {
                    model.w_company = row["w_company"].ToString();
                }
                if (row["w_name1"] != null)
                {
                    model.w_name1 = row["w_name1"].ToString();
                }
                if (row["w_name2"] != null)
                {
                    model.w_name2 = row["w_name2"].ToString();
                }
                if (row["w_code1"] != null)
                {
                    model.w_code1 = row["w_code1"].ToString();
                }
                if (row["w_code2"] != null)
                {
                    model.w_code2 = row["w_code2"].ToString();
                }
                if (row["w_question"] != null)
                {
                    model.w_question = row["w_question"].ToString();
                }
                if (row["content"] != null)
                {
                    model.content = row["content"].ToString();
                }
                if (row["d_signature"] != null)
                {
                    model.d_signature = row["d_signature"].ToString();
                }
                if (row["b_signature"] != null)
                {
                    model.b_signature = row["b_signature"].ToString();
                }
                if (row["j_signature"] != null)
                {
                    model.j_signature = row["j_signature"].ToString();
                }
                if (row["sub_time"] != null && row["sub_time"].ToString() != "")
                {
                    model.sub_time = DateTime.Parse(row["sub_time"].ToString());
                }
                if (row["instance_id"] != null && row["instance_id"].ToString() != "")
                {
                    model.instance_id = int.Parse(row["instance_id"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,ask_time,end_time,how,ask_address,b_name,b_sex,b_age,b_code,b_company,b_zhiwu,b_zhuzhi,b_mobile,d_name,d_code,d_note,d_there,d_company,w_company,w_name1,w_name2,w_code1,w_code2,w_question,content,d_signature,b_signature,j_signature,sub_time,instance_id  ");
            strSql.Append(" FROM " + databaseprefix + "interview ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,ask_time,end_time,how,ask_address,b_name,b_sex,b_age,b_code,b_company,b_zhiwu,b_zhuzhi,b_mobile,d_name,d_code,d_note,d_there,d_company,w_company,w_name1,w_name2,w_code1,w_code2,w_question,content,d_signature,b_signature,j_signature,sub_time,instance_id  ");
            strSql.Append(" FROM " + databaseprefix + "interview ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM " + databaseprefix + "interview ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from " + databaseprefix + "interview T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "interview");
            strSql.Append(" where " + strWhere);
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        #endregion
    }
}
